--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_CPT_FAMTVA AS (SELECT * FROM &erp.CPT_FAMTVA WHERE 1=2);




DELETE FROM se_CPT_FAMTVA;

 INSERT INTO se_CPT_FAMTVA
 (SELECT DISTINCT SubStr(famcpta,2,1) tvatiers_cod,
         SubStr(famcpta,3,1) tvaart_cod,
         Decode(p.x,0,'E',1,'D') tvaregime_cod,
         0 enreg_id,
         famcpta famtvav_cod,
         NULL famtvaa_cod
    FROM uv_cimputations i, &outils.pivot p
    WHERE famcpta LIKE 'T%' AND famcpta NOT LIKE 'TP%'
      AND p.x<2) ;


 UPDATE se_CPT_FAMTVA tf SET enreg_id=(select enreg_id FROM &erp.CPT_FAMTVA f WHERE f.tvatiers_cod=tf.tvatiers_cod AND f.tvaart_cod=tf.tvaart_cod AND f.tvaregime_cod =tf.tvaregime_cod) ;
 UPDATE se_CPT_FAMTVA tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('tvaart_cod@tvatiers_cod@tvaregime_cod','CPT_FAMTVA','O') ;

 commit;



select * from (
SELECT &outils.stragg(source) source,tvatiers_cod, tvaart_cod, tvaregime_cod, enreg_id, famtvav_cod, famtvaa_cod
FROM (
  SELECT 'uv' source ,g.* FROM se_CPT_FAMTVA g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.CPT_FAMTVA g
  )
GROUP BY tvatiers_cod, tvaart_cod, tvaregime_cod, enreg_id, famtvav_cod, famtvaa_cod
HAVING Count(1)!=2) r
ORDER BY tvatiers_cod, tvaart_cod;